import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

import os.path as osp
from helper import *
from write_to_excel import write_to_excel


def calculate_excel(file_path):
    verify_legitimacy(file_path)  # 验证第一和第二页帧合法性
    weight_df, obj_weight_df, query = get_weight_df(file_path)  # 从课程目标达成情况报告页帧获取权重值和所有客观评价和非客观评价页帧名,班级号
    score_ach_dfs = get_score_ach_dfs(file_path, weight_df, obj_weight_df)  # 对每个页帧的分数和达成度进行计算处理并生成报告
    report_df = get_report_df(file_path, score_ach_dfs, weight_df, obj_weight_df, query=query)  # 对报告进行处理输出相关信息
    summary_df, goal_weights = get_summary_df(file_path, score_ach_dfs)  # 计算出达成度散点图页帧的平均值和达成度信息
    statistic_df = get_statistic_df(summary_df)  # 绘出达成度散点图页帧中的统计信息
    diagrams = display_diagram(summary_df)  # 得到散点图
    write_to_excel(file_path,  # 把计算得到的数据写入excel
                   score_ach_dfs,
                   obj_weight_df,
                   report_df,
                   summary_df,
                   goal_weights,
                   statistic_df,
                   diagrams)


# 验证第一和第二页帧合法性
def verify_legitimacy(file_path):
    first_sheet_df = pd.read_excel(io=file_path, sheet_name=FIRST_SHEET_NAME)
    second_sheet_df = pd.read_excel(io=file_path, sheet_name=SECOND_SHEET_NAME)
    (_, yi_1st), _ = loc_by_value(first_sheet_df, '课程编号')[0]
    (xi_2nd, _), _ = loc_by_value(second_sheet_df, '考核方式')[0]
    first_tem_df = first_sheet_df.iloc[:, yi_1st:]
    first_tem_df = first_tem_df.dropna(axis=1, how='all')
    first_tem_df = first_tem_df.dropna(axis=0, how='all')
    second_tem_df = second_sheet_df.iloc[0:xi_2nd, :]
    second_tem_df = second_tem_df.dropna(axis=1, how='all')
    second_tem_df = second_tem_df.dropna(axis=0, how='all')

    def _verify(title):
        first_info, second_info = loc_by_value(first_tem_df, title), loc_by_value(second_tem_df, title)
        if not first_info or not second_info:
            return False
        (xi1, yi1), _ = first_info[0]
        (xi2, yi2), _ = second_info[0]
        return first_tem_df.iloc[xi1, yi1 + 1] == second_tem_df.iloc[xi2, yi2 + 1]

    result = ''
    if not _verify('课程编号'):
        result = result + '课程编号 缺失或内容不一致！\n'
    if not _verify('课程名称'):
        result = result + '课程名称 缺失或内容不一致！\n'
    if not _verify('开课学期'):
        result = result + '开课学期 缺失或内容不一致！\n'
    if not _verify('任课教师'):
        result = result + '任课教师 缺失或内容不一致！\n'
    if not len(result) == 0:
        logger.error(result)
        raise ValueError(result)
    # 测试两种考核方式的目标数是否合法
    exam_pattern = loc_by_value(second_sheet_df, '考核方式')
    (exam1_xi, exam1_yi), _ = exam_pattern[0]
    (exam2_xi, _), _ = exam_pattern[1]
    df1_list = second_sheet_df.iloc[exam1_xi, :].dropna().to_list()
    df2_list = second_sheet_df.iloc[exam2_xi, :].dropna().to_list()
    if not df1_list == df2_list:
        result = '考核方式内容或数量不一致！'
        logger.error(result)
        raise ValueError(result)
    # 测试权重和是否合法
    obj_weight_sum_series = second_sheet_df.iloc[exam1_xi + 1:exam2_xi - 1, exam1_yi + 1:].dropna(axis=1).sum(axis=0)
    obj_weight_sum = len(obj_weight_sum_series[obj_weight_sum_series == 1])  # 统计权重和为1的个数
    obj_weight_num = len(obj_weight_sum_series)  # 目标数
    if not obj_weight_sum == obj_weight_num:
        result = '客观评价中各目标的权重和含有不为1的情况！'
        logger.error(result)
        raise ValueError(result)
    (all_weight_xi, all_weight_yi), _ = loc_by_value(second_sheet_df, '权重值')[-1]
    all_weight_sum = round(second_sheet_df.iloc[all_weight_xi + 1:, all_weight_yi].dropna(axis=0).sum(), 3)
    if not all_weight_sum == 1:
        result = '总权重和含有不为1！'
        logger.error(result)
        raise ValueError(result)


# 从课程目标达成情况报告页帧获取权重值
def get_weight_df(file_path):
    sheet_df = pd.read_excel(io=file_path, sheet_name=SECOND_SHEET_NAME)
    (class_xi, class_yi), _ = loc_by_value(sheet_df, '班级')[0]
    (exam_xi, exam_yi), (_, exam_y) = loc_by_value(sheet_df, '考核方式')[0]
    class_id = int(sheet_df.iloc[class_xi, class_yi + 3])
    (obj_weight_xi, _), _ = loc_by_value(sheet_df, '权重和')[0]
    obj_weight_df = sheet_df.iloc[exam_xi + 1:obj_weight_xi, :]
    obj_weight_df.set_index(exam_y, inplace=True)
    obj_weight_df = obj_weight_df.dropna(axis=1, how='all')
    obj_weight_df = obj_weight_df.fillna(0)
    columns = sheet_df.iloc[exam_xi, exam_yi + 1:].dropna().to_list()[0:obj_weight_df.shape[1]]
    (weight_row_start, _), (_, exam_y) = loc_by_value(sheet_df, '课程目标达成度情况')[0]
    (weight_row_end, _), _ = loc_by_value(sheet_df, '审核时间')[0]
    weight_df = sheet_df.iloc[weight_row_start:weight_row_end, :]
    weight_df.set_index(exam_y, inplace=True)
    (weight_row_start, iy), _ = loc_by_value(weight_df, '权重值')[0]
    weight_df = weight_df.iloc[weight_row_start + 1:, iy].dropna()
    all_weight_df = obj_weight_df.copy()
    for index in weight_df.index:
        goal_weight = weight_df.loc[index]
        if '考核成绩法' in index:
            if not goal_weight == 0:  # 该目标客评页帧权重不为0,增加非客评行
                all_weight_df.loc[obj_weight_df.index] = all_weight_df.loc[obj_weight_df.index] * goal_weight
            else:  # 该目标客评页帧权重为0,删掉所有客评行,这种奇葩我相信是没有的~
                all_weight_df.drop(obj_weight_df.index, inplace=True)
                obj_weight_df.drop(obj_weight_df.index, inplace=True)
        elif not goal_weight == 0:
            all_weight_df.loc[index.replace('\n', ' ')] = goal_weight
    all_weight_df.columns = columns
    obj_weight_df.columns = columns
    return all_weight_df, obj_weight_df, class_id


# 页帧处理
def get_score_ach_dfs(file_path, weight_df, weight_obj_df):
    file_name = osp.basename(file_path)
    stu_info, class_ids = get_class_info(file_path)
    obj_num = weight_obj_df.shape[0]  # 客观评价页帧数
    ach_dict, ach_mean_dict = {SECOND_SHEET_NAME: weight_df}, {}
    score_dict, score_mean_dict = {SECOND_SHEET_NAME: weight_df}, {}
    for sheet_name in weight_df.index[:obj_num]:
        sheet_df = pd.read_excel(io=file_path, sheet_name=sheet_name)
        obj_ref = get_obj_ref(sheet_df, weight_df, sheet_name)
        obj_score_df = get_obj_score(sheet_df, obj_ref, stu_info, sheet_name)
        obj_ach_df = get_ach_df(obj_score_df, obj_ref, sheet_name)
        ach_dict[sheet_name] = obj_ach_df
        score_dict[sheet_name] = obj_score_df
        ach_mean_df = get_class_mean(obj_ach_df)
        ach_mean_dict[sheet_name] = ach_mean_df
        score_mean_df = get_class_mean(obj_score_df)
        score_mean_dict[sheet_name] = score_mean_df
        logger.debug('{}中的{}页帧平均分：\n{}'.format(file_name, sheet_name, score_mean_df))
        logger.debug('{}中的{}页帧平均达成度：\n{}'.format(file_name, sheet_name, ach_mean_df))

    for sheet_name in weight_df.index[obj_num:]:
        sheet_df = pd.read_excel(io=file_path, sheet_name=sheet_name)
        sub_ref = get_sub_ref(sheet_df, weight_df, sheet_name)
        sub_score_df = get_sub_score(sheet_df, sub_ref, stu_info, sheet_name)
        sub_ach_df = get_ach_df(sub_score_df, sub_ref, sheet_name)
        ach_dict[sheet_name] = sub_ach_df
        score_mean_df = get_class_mean(sub_score_df)
        ach_mean_df = get_class_mean(sub_ach_df)
        ach_mean_dict[sheet_name] = ach_mean_df
        score_dict[sheet_name] = sub_score_df
        score_mean_dict[sheet_name] = score_mean_df
        logger.debug('{}中的{}页帧平均达成度：\n{}'.format(file_name, sheet_name, ach_mean_df))

    return {SCORE_INFO: score_dict, SCORE_MEAN_INFO: score_mean_dict,
            ACH_INFO: ach_dict, ACH_MEAN_INFO: ach_mean_dict,
            CLASS_IDS: class_ids}


# 获得班级和学号
def get_class_info(file_path):
    sheet_df = pd.read_excel(io=file_path, sheet_name=CLASS_ID_SHEET_IDX)
    stu_info = loc_by_value(sheet_df, '班级')
    (xi, yi), _ = stu_info[0]
    class_ids = sheet_df.iloc[xi + 1:, yi].dropna(axis=0, how='any').astype('int').to_list()
    stu_info = loc_by_value(sheet_df, '学生学号')
    (xi, yi), _ = stu_info[0]
    stu_ids = sheet_df.iloc[xi + 1:, yi].dropna(axis=0, how='any').astype('int').to_list()
    stu_info = pd.MultiIndex.from_arrays([class_ids, stu_ids])
    class_ids_dict = {}
    for class_id in class_ids:
        class_ids_dict[class_id] = class_ids_dict.get(class_id, 0) + 1
    return stu_info, class_ids_dict


# 生成报告
def get_report_df(file_path, score_ach_dfs, weight_df, obj_weight_df, query=None, decimal=2):
    report_dic = score_ach_dfs[ACH_MEAN_INFO].copy()
    file_name = osp.basename(file_path)
    obj_num = obj_weight_df.shape[0]
    keys = list(report_dic.keys())
    df_merge = report_dic.pop(keys[0])
    for key, df in report_dic.items():
        df_merge = pd.merge(df_merge, df, left_index=True, right_index=True)
    report_dfs = []
    for goal_id, df in df_merge.groupby(df_merge.columns.get_level_values(1), axis=1):
        df.columns = df.columns.get_level_values(0)
        lv1_index = df.index
        lv2_index = [goal_id] * len(df.index)
        df.index = pd.MultiIndex.from_arrays([lv1_index, lv2_index], names=('班级', '目标'))
        report_dfs.append(df)
    report_df = pd.concat(report_dfs, axis=0, join='outer')  # 按列索引对齐
    report_df.sort_index(inplace=True)
    report_df.fillna(0, inplace=True)
    report_df = report_df[weight_df.index]  # 行索引按weight_df.index排序
    report_df.insert(obj_num, '考核成绩法', 0, allow_duplicates=False)  # 在所有客观评价包含的页帧后添加'达成度 （考核成绩法）'列，初始值为0
    report_df.insert(report_df.shape[1], '综合加权', 0, allow_duplicates=False)  # 总表追加'达成度 （综合加权）'列，初始值为0
    report_df = report_df.T
    obj_report_df = report_df.iloc[:obj_num, :]
    summary_report_df = report_df.iloc[obj_num:, :].copy()
    for class_id, goal_id in summary_report_df.columns:
        summary_report_df.loc['考核成绩法', (class_id, goal_id)] = (obj_report_df[(class_id, goal_id)] * obj_weight_df[goal_id]).sum(axis=0)
        summary_report_df.loc['综合加权', (class_id, goal_id)] = (report_df[(class_id, goal_id)] * weight_df[goal_id]).sum(axis=0)
    all_report_df = pd.concat([obj_report_df, summary_report_df], axis=0, join='inner')
    if query is None:
        logger.info('{}中的报告信息：\n{}'.format(file_name, all_report_df.round(decimal)))
        return all_report_df
    else:
        class_ids = set(all_report_df.columns.get_level_values(0))
        if query in class_ids:
            result_df = all_report_df[query]
            logger.info('\"{}\" 中的 {}班级信息：\n{}'.format(file_name, query, result_df.round(decimal)))
            return result_df
        else:
            logger.warning('\""{}\" 中没有发现班级{}  所有参考: {}'.format(file_name, query, class_ids))


# 实现达成度散点图页帧中的功能
def get_summary_df(file_path, score_ach_dfs):
    file_name = osp.basename(file_path)
    result = score_ach_dfs[ACH_INFO].copy()
    weight_df = result.pop(SECOND_SHEET_NAME)  # result中'课程目标达成情况报告'页帧已被删除
    keys = list(result.keys())
    df_merge = result.pop(keys[0])
    for key, df in result.items():
        df_merge = pd.merge(df_merge, df, left_index=True, right_index=True)
    goal_dfs = []
    goal_weights = []
    lv2_columns = df_merge.columns.get_level_values(1)
    for goal_id, df in df_merge.groupby(lv2_columns, axis=1):
        id_weight = weight_df[goal_id]
        goal_weight = id_weight[id_weight > 0].to_list()
        calculation_size = df.shape[1] - 1
        arc_name = '目标达成度'
        mean_name = '平均值'
        df[(arc_name, goal_id)] = 0
        df[(mean_name, goal_id)] = 0
        goal_weight = goal_weight + [np.nan] * (df.shape[1] - len(goal_weight))
        goal_weights.extend(goal_weight)
        for i, (column_name, column) in enumerate(df.iteritems()):
            try:
                df[(arc_name, goal_id)] = df[(arc_name, goal_id)] + column * goal_weight[i]
            except IndexError:
                logger.error(f'{file_name}中的{goal_id}页帧错乱')
                break
            if i == calculation_size:  # 新增列不参与计算
                break
        df[(mean_name, goal_id)] = df[(arc_name, goal_id)].mean(axis=0)
        df = df.swaplevel(i=0, j=1, axis=1)
        goal_dfs.append(df)
    summary_df = pd.concat(goal_dfs, axis=1)
    summary_df['达成值'] = 0.7
    goal_weights.extend([np.nan])
    return summary_df, goal_weights


# 得到达成度散点图页帧中的统计数据
def get_statistic_df(summary_df):
    result = {}
    re_index = ['超过平均值人数', '超过达成值人数', '低于达成值人数', '所有目标超过平均值人数', '所有目标超过达成值人数', '所有目标低于达成值人数']
    ach_value = summary_df[('达成值', '')]
    level1_index = []  # 一级索引获取，不包含'达成值'列
    for multi_index in summary_df.columns:
        if multi_index[0] not in level1_index and not multi_index[0] == '达成值':
            level1_index.append(multi_index[0])
    all_over_aver_condition = True  # 所有目标超过平均值人数统计条件,由Bool=>Series类
    all_over_ach_condition = True  # 所有目标均达成人数统计条件
    all_under_ach_condition = True  # 所有目标均未达成人数统计条件
    for index in level1_index:
        sig_over_aver = summary_df[summary_df[(index, '目标达成度')] >= summary_df[(index, '平均值')]].shape[0]
        sig_over_ach = summary_df[summary_df[(index, '目标达成度')] >= ach_value].shape[0]
        sig_under_ach = summary_df[summary_df[(index, '目标达成度')] < ach_value].shape[0]
        all_over_aver_condition = all_over_aver_condition & (summary_df[(index, '目标达成度')] >= summary_df[(index, '平均值')])
        all_over_ach_condition = all_over_ach_condition & (summary_df[(index, '目标达成度')] >= ach_value)
        all_under_ach_condition = all_under_ach_condition & (summary_df[(index, '目标达成度')] < ach_value)
        result[index] = [sig_over_aver, sig_over_ach, sig_under_ach]
    all_over_aver = summary_df[all_over_aver_condition].shape[0]
    all_over_ach = summary_df[all_over_ach_condition].shape[0]
    all_under_ach = summary_df[all_under_ach_condition].shape[0]
    for i, key in enumerate(result.keys()):
        if i == 0:
            result[key].extend([all_over_aver, all_over_ach, all_under_ach])
        else:
            result[key].extend([np.nan] * 3)
    result_df = pd.DataFrame(data=result, index=re_index)
    logger.info('统计信息：\n{}'.format(result_df))
    return result_df


# 得到达成度散点图页帧中的散点图
def display_diagram(summary_df):
    ach_value = summary_df[('达成值', '')]
    x_axis = range(summary_df.shape[0])  # x轴
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False
    level1_index = []  # 一级索引获取，不包含'达成值'列
    figs = []
    for multi_index in summary_df.columns:
        if multi_index[0] not in level1_index and not multi_index[0] == '达成值':
            level1_index.append(multi_index[0])
    for index in level1_index:
        fig = plt.figure()
        plt.title(index)
        plt.plot(x_axis, summary_df[(index, '目标达成度')], "oc", label='学生达成度')
        plt.plot(x_axis, summary_df[(index, '平均值')], "b", label='均值')
        plt.plot(x_axis, ach_value, 'r', label='达成值')
        plt.legend()
        figs.append(fig)
        # plt.show()
    return figs


# 处理客观页帧，得到小题分数在每个目标上分配信息的dataframe
def get_obj_ref(df, weight_df, sheet_name):
    (goal_ix, goal_iy), _ = loc_by_value(df, '小题分数')[0]
    (stu_name_xi, _), _ = loc_by_value(df, '学生姓名')[0]
    (_, total_score_iy), _ = loc_by_value(df, '总分')[0]
    index = df.iloc[goal_ix + 1:stu_name_xi, goal_iy].dropna().to_list()
    weight_df_columns = list(weight_df.columns)
    # 目标数合法性判断
    if not set(index) == set(weight_df_columns):
        result = f'”{sheet_name}页帧“ 中的目标数和 ”{SECOND_SHEET_NAME}页帧“ 定义的目标不一致！'
        logger.error(result)
        raise ValueError(result)
    goal_num = len(index)
    label_prop_df = df.iloc[goal_ix + 1:goal_ix + 1 + goal_num, goal_iy + 1:total_score_iy]
    label_prop_df.index = index
    tem_df = label_prop_df.where(label_prop_df == SCORE_LABEL).stack()
    need_label_num, count_label_num = label_prop_df.shape[1], len(tem_df)  # 所需label数量和表中label数量
    with_label_columns = len(set(tem_df.index.get_level_values(1)))  # 含有label的列数
    # 满分合法性判断
    if not need_label_num == count_label_num == with_label_columns:
        result = f'”{sheet_name}页帧“ 中错误的1分布！各小题总分不是满分。'
        logger.error(result)
        raise ValueError(result)
    reference_df = df.iloc[goal_ix:goal_ix + 1, goal_iy + 1:total_score_iy]  # 只有小题分数一行
    reference_df.index = ['小题分数']
    reference_df.columns = (label_prop_df == 1).idxmax().to_list()
    return reference_df


# 处理非客观页帧，得到小题分数在每个目标上分配信息的dataframe
def get_sub_ref(sheet_df, weight_df, sheet_name):
    goal_num = weight_df.shape[1]
    (full_score_ix, full_score_iy), _ = loc_by_value(sheet_df, '参数')[0]
    columns = sheet_df.iloc[full_score_ix, full_score_iy + 2:].dropna(axis=0).to_list()
    weight_df_columns = list(weight_df.columns)
    # 目标数合法性判断
    if not len(columns) == len(weight_df_columns):  # set(columns) == set(weight_df_columns):
        result = f'”{sheet_name}页帧“中{columns}和 ”{SECOND_SHEET_NAME}页帧“ 中{weight_df_columns}的内容或数量需要一致！'
        logger.error(result)
        raise ValueError(result)
    ref = sheet_df.iloc[full_score_ix + 1, full_score_iy + 2:].dropna()
    # 满分合法性判断
    if not len(ref) == goal_num:
        result = f'”{sheet_name}页帧“ 中 1分布错误！'
        logger.error(result)
        raise ValueError(result)
    ref = pd.DataFrame(ref).T
    ref.columns = weight_df_columns
    ref.index = ['满分']
    return ref


# 得到全部班级在各个目标上分数的dataframe，行索引对应班级，列索引对应目标
def get_obj_score(sheet_df, ref_df, class_info, sheet_name):
    _, (_, stu_id_y) = loc_by_value(sheet_df, '学生学号')[0]
    _, (_, stu_name_y) = loc_by_value(sheet_df, '学生姓名')[0]
    (_, total_score_iy), (_, total_score_y) = loc_by_value(sheet_df, '总分')[0]
    score_df = sheet_df.set_index(inplace=False, keys=stu_id_y)
    stu_ids = class_info.get_level_values(1)
    score_df = score_df.loc[stu_ids, stu_name_y:total_score_y].iloc[:, 1:-1]
    score_df.index = class_info
    ref_columns = ref_df.columns  # 默认得分区和表头数据对齐
    score_df.columns = pd.MultiIndex.from_arrays([[sheet_name] * len(ref_columns), ref_columns], names=('表单', '目标'))
    data = {}
    for goal_id, sheet_df in score_df.groupby(score_df.columns, axis=1):
        data[goal_id] = sheet_df.sum(axis=1)  # 全部列求和
    result = pd.DataFrame(data)
    return result


# 非客观评价页帧达成度计算
def get_sub_score(sheet_df, ref_df, class_info, sheet_name):
    goal_num = ref_df.shape[1]
    _, (_, stu_id_y) = loc_by_value(sheet_df, '学生学号')[0]
    _, (_, stu_name_y) = loc_by_value(sheet_df, '学生姓名')[0]
    score_df = sheet_df.set_index(inplace=False, keys=stu_id_y)
    stu_ids = class_info.get_level_values(1)
    score_df = score_df.loc[stu_ids, stu_name_y:].iloc[:, 1:1 + goal_num]
    score_df.index = class_info
    score_df.columns = pd.MultiIndex.from_arrays([[sheet_name] * len(ref_df.columns), ref_df.columns], names=('表单', '目标'))
    return score_df


# 得到全部班级在各个目标上达成度的dataframe，行索引对应班级id，列索引对应目标id
def get_ach_df(score_df, ref_df, sheet_name):
    score_df = score_df.copy()
    sum_dict = {}
    for obj, df in ref_df.groupby(ref_df.columns, axis=1):
        sum_dict[obj] = df.sum(axis=1)[0]  # series值唯一，直接取出来
    for obj_id, value in sum_dict.items():
        if value == 0:
            score_df[(sheet_name, obj_id)] = 0
        else:
            score_df[(sheet_name, obj_id)] = score_df[(sheet_name, obj_id)] / value
    return score_df


# 页帧按分数和达成度按班级求平均
def get_class_mean(df):
    class_mean = {}
    for obj_id, df in df.groupby(df.index.get_level_values(0), axis=0):  # 按班级分组
        class_mean[obj_id] = df.mean(axis=0)  # 全部列平均
    return pd.DataFrame(class_mean).T  # 我习惯将目标id作为列索引，转不转置无所谓
